<?php
declare (strict_types = 1);

namespace app\controller;

use think\Request;
use app\BaseController;
use think\facade\View;
use app\middleware\CheckLogin;
use app\model\ServiceCustomer as CustomerModel;
use app\model\SettingCar as CarModel;
use app\model\SettingUser as UserModel;
use PHPExcel_IOFactory;

class Service extends BaseController
{

    // 注册控制器中间件
    protected $middleware = [CheckLogin::class];

    //主页
    public function index()
    {
        return redirect((string)url('Service/unperfected'));
    }

    /**
     * 未完善的客户列表.
     *
     * @return \think\Response
     */
    public function unperfected()
    {
        ///获取未完善数据
        $data = CustomerModel::where('perfected',0)->select();
        View::assign('data',$data);

        return view('unperfected'); 
    }

    /**
     * 保有客户数据列表
     *
     * @return \think\Response
     */
    public function perfected()
    {
        return view('perfected'); 
    }

    /**
     * 保有客户数据json列表
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function perfectedJson(Request $request)
    {

        //获取传来的条件数据
        $request = $request->param();

        // //返回让表格绘制表格的指令
        $data['draw'] = intval($request['draw']);

        //总记录数
        $data['recordsTotal'] = CustomerModel::where('perfected',1)->count();

            //记录起
        $start = $request['start'];

        //查询长度
        $length = $request['length'];

        //排序条件
        $order = $request['order']['0']['column'];

        // //排序条件
        $orderArr = [ 0 =>'deliver_date', 1 =>'sales', 4 =>'vehicle'];

        //获取要排序的字段
        $orderField = (empty($orderArr[$order]))?'deliver_date':$orderArr[$order];

        //排序方式
        $asc = $request['order']['0']['dir'];

        //获取搜索后记录数
        if(!empty($request['search']['value'])){
            $research = CustomerModel::where('sales|owner_name|user_phone|vin','like','%'.$request['search']['value'].'%')->where('perfected',1)->select();
            $data['recordsFiltered'] = count($research);
        }else{
            $data['recordsFiltered'] = $data['recordsTotal'];
        }

        //获取保有客户数据
        $customerData = CustomerModel::field('id,deliver_date,sales,owner_name,user_phone,vehicle,config,color,trim,vin')->where('sales|owner_name|user_phone|vin','like','%'.$request['search']['value'].'%')->where('perfected',1)->order($orderField,$asc)->limit(intval($start), intval($length))->select();

        for ($i=0; $i < count($customerData); $i++) { 
            $customerData[$i]['deliver_date'] = date('Y/m/d',strtotime($customerData[$i]['deliver_date']));
            $customerData[$i]['owner_name'] = mb_substr($customerData[$i]['owner_name'],0,6);
        }

        $data['data'] = $customerData;

        return json($data);
        // dump($data);

    }

    /**
     * 显示指定的资源
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function read($id)
    {
        //获取此前数据
        $data = CustomerModel::find($id);

        //转换性别
        if($data['owner_sex'] == 1){
            $data['owner_sex'] = "男";
        }else{
            $data['owner_sex'] = "女";
        }

        View::assign('data',$data);

        return view('read'); 
    }

    /**
     * 显示编辑资源表单页.
     *
     * @param  \think\Request  $request
     * @param  int  $id
     * @return \think\Response
     */
    public function edit(Request $request, $id)
    {   
        //获取此前数据
        $data = CustomerModel::find($id);
        View::assign('data',$data);

        //过往最大的档案编号
        $lastFileNo = CustomerModel::max('file_no');
        if(empty($lastFileNo)){
            $lastFileNo = '无';
        }
        View::assign('lastFileNo',$lastFileNo);

        //获取过往职业数据
        $occupation = CustomerModel::Distinct(true)->column('owner_occupation');
        View::assign('occupation',$occupation);

        //获取销售顾问数据
        $sales = UserModel::where('visible',1)->column('name');
        View::assign('sales',$sales);

        //获取车系数据
        $vehicleOption = CarModel::where('valid','1')->Distinct(true)->column('vehicle');
        View::assign('vehicleOption',$vehicleOption);

        //获取配置数据
        $configOption = CarModel::where('valid','1')->where('vehicle',$data['vehicle'])->Distinct(true)->column('config');
        View::assign('configOption',$configOption);

        //确定当前车型
        $carWhere['vehicle'] = $data['vehicle'];
        $carWhere['config'] = $data['config'];

        //获取当前车型配置数据
        $carData = CarModel::where($carWhere)->find();
        if(!empty($carData)){
            //获取当前车型颜色数据
            $colorOption = explode(',',$carData['color']);
            View::assign('colorOption',$colorOption);

            //获取当前车型内饰数据
            $trimOption = explode(',',$carData['trim']);
            View::assign('trimOption',$trimOption);
        }else{
            $colorOption = '配置输入有误';
            View::assign('colorOption',$colorOption);
            $trimOption = '配置输入有误';
            View::assign('trimOption',$trimOption);
        }

        //获取配置数据
        $insurance_company_list = CustomerModel::Distinct(true)->column('insurance_company');
        View::assign('insurance_company_list',$insurance_company_list);

        //模式是完善还是编辑
        $type = $request->param('type');
        if($type == 'edit'){
            return view('edit'); 
        }else{
            return view('perfect'); 
        }
        
    }

    /**
     * 保存更新的资源
     *
     * @param  \think\Request  $request
     * @param  int  $id
     * @return \think\Response
     */
    public function update(Request $request, $id)
    {
        $data = $request->param();

        //转换性别
        if($data['owner_sex'] == "男"){
            $data['owner_sex'] = 1;
        }else{
            $data['owner_sex'] = 0;
        }

        //已完善设置为1
        $data['perfected'] = 1;

        $result = CustomerModel::update($data);//调用create方法写入数据库

        if($result == false){
            return 0;
        }else{
            return $result->id;
        }
    }

    /**
     * 删除指定资源
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function delete($id)
    {
        //删除
        $user = CustomerModel::find($id);//删除id号
        $result = $user->delete();//确认删除结果，返回布尔值
        if(!$result){
            return "ok";//成功则返回OK
        }else{
            return "false";//失败则返回false
        }
    }

    /**
     * 批量模板导入数据的资源
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    
    public function input(Request $request)
    {
        // 获取表单上传文件 例如上传了001.jpg
        $file = request()->file('input-excel');

        //判断是否有文件上传
        if(!empty($file)){
            // 上传到本地服务器
            $savename = \think\facade\Filesystem::disk('temp')->putFile('CustomerExcel',$file);
            // //获取文件路径
            $path = public_path().'runtime/temp/'.$savename;

            $objPHPExcel = PHPExcel_IOFactory::load($path,$encode = 'utf-8');
     
            $sheet = $objPHPExcel->getSheet(0)->toArray();

            //检查模板有没有问题
            if($sheet[0][5] != "身份证号码*" || $sheet[0][21] != "车架号*"){
                $return['result'] = false;
                $return['reason'] = '模板错误';
                $return['count'] = 0;
                $return['blankRow'] = 0;
            }else{

                $isEmpty = false;
                // 检查数据
                for ($i=1; $i < count($sheet); $i++) { 
                    if(empty($sheet[$i][0])){
                        $return['result'] = false;
                        $return['reason'] = '档案编号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21]; 
                    }
                    if(empty($sheet[$i][1])){
                        $return['result'] = false;
                        $return['reason'] = '提车时间不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][2])){
                        $return['result'] = false;
                        $return['reason'] = '销售顾问不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][3])){
                        $return['result'] = false;
                        $return['reason'] = '车主名称不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][4])){
                        $return['result'] = false;
                        $return['reason'] = '车主联系电话不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][5])){
                        $return['result'] = false;
                        $return['reason'] = '车主身份证号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][8])){
                        $return['result'] = false;
                        $return['reason'] = '车主地址不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][9])){
                        $return['result'] = false;
                        $return['reason'] = '省份不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][10])){
                        $return['result'] = false;
                        $return['reason'] = '市份不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][11])){
                        $return['result'] = false;
                        $return['reason'] = '区县不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][15])){
                        $return['result'] = false;
                        $return['reason'] = '车系不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][16])){
                        $return['result'] = false;
                        $return['reason'] = '配置不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][17])){
                        $return['result'] = false;
                        $return['reason'] = '颜色不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][19])){
                        $return['result'] = false;
                        $return['reason'] = '购车价格不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][20])){
                        $return['result'] = false;
                        $return['reason'] = '车型编号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][21])){
                        $return['result'] = false;
                        $return['reason'] = '车架号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                    if(empty($sheet[$i][22])){
                        $return['result'] = false;
                        $return['reason'] = '发动机号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $return['errorRow'] = $i+1;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][21];
                    }
                }

                if($isEmpty == false){
                    $data = array();
                    for ($i=1; $i < count($sheet); $i++) {
                        $vin = $sheet[$i][21];
                        //判断这一行是否有车架号信息
                        $blankRow = 0;
                        if(!empty($vin)){
                            //以车架号判断是否有重复的车辆信息
                            $old = CustomerModel::where('vin',$vin)->find();
                            if(empty($old)){
                                $data[$i]['file_no'] = trim((String)$sheet[$i][0]);
                                $data[$i]['deliver_date'] = date("Y-m-d",strtotime($sheet[$i][1]));
                                $data[$i]['sales'] = trim((String)$sheet[$i][2]);
                                $data[$i]['owner_name'] = trim((String)$sheet[$i][3]);
                                $data[$i]['owner_phone'] = trim((String)$sheet[$i][4]);
                                $data[$i]['owner_id'] = trim((String)$sheet[$i][5]);
                                //转换性别
                                if($sheet[$i][6] == "男"){
                                    $data[$i]['owner_sex'] = 1;
                                }else{
                                    $data[$i]['owner_sex'] = 0;
                                }
                                //如果生日为空
                                if(!empty($sheet[$i][7])){
                                    $data[$i]['owner_birth'] = date("Y-m-d",strtotime($sheet[$i][7]));
                                }

                                $data[$i]['owner_address'] = trim((String)$sheet[$i][8]);
                                $data[$i]['owner_province'] = trim((String)$sheet[$i][9]);
                                $data[$i]['owner_city'] = trim((String)$sheet[$i][10]);
                                $data[$i]['owner_county'] = trim((String)$sheet[$i][11]);
                                // 如果职业不为空
                                if(!empty($sheet[$i][12])){
                                    $data[$i]['owner_occupation'] = trim((String)$sheet[$i][12]);
                                }else{
                                    $data[$i]['owner_occupation'] = '未填写';
                                }
                                $data[$i]['user_name'] = trim((String)$sheet[$i][13]);
                                $data[$i]['user_phone'] = trim((String)$sheet[$i][14]);
                                $data[$i]['vehicle'] = trim((String)$sheet[$i][15]);
                                $data[$i]['config'] = trim((String)$sheet[$i][16]);
                                $data[$i]['color'] = trim((String)$sheet[$i][17]);
                                $data[$i]['trim'] = trim((String)$sheet[$i][18]);
                                $data[$i]['final_price'] = trim((String)$sheet[$i][19]);
                                $data[$i]['model'] = trim((String)$sheet[$i][20]);
                                $data[$i]['vin'] = trim((String)$sheet[$i][21]);
                                $data[$i]['engine'] = trim((String)$sheet[$i][22]);
                                // 如果车牌不为空
                                if(!empty($sheet[$i][23])){
                                    $data[$i]['plate_number'] = trim((String)$sheet[$i][23]);
                                }
                                // 如果保险公司不为空
                                if(!empty($sheet[$i][24])){
                                    $data[$i]['insurance_company'] = trim((String)$sheet[$i][24]);
                                }
                                //如果保险到空日期不为空
                                if(!empty($sheet[$i][25])){
                                    $data[$i]['insurance_expirydate'] = date("Y-m-d",strtotime($sheet[$i][25]));
                                }

                                $data[$i]['perfected'] = 1;
                            }
                        }else{
                            $blankRow = $blankRow + 1;
                        }  
                    }
                    $user = new CustomerModel;
                    $saveResult = $user->saveAll($data);
                    $return['count'] = count($saveResult);
                    $return['result'] = true;
                    $return['reason'] = '没有错误';
                    $return['blankRow'] = $blankRow;
                }
            }
        }else{
            $return['result'] = false;
            $return['reason'] = '未上传文件';
            $return['count'] = 0;
            $return['blankRow'] = 0;
        }
        
        return json($return);
    }

    /**
     * 批量导出数据的资源
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */

    public function output()
    {
        //获取保有客户的数据
        $data = CustomerModel::where('perfected',1)->order('deliver_date','asc')->select();

        //创建PHPExcel对象
        $objPHPExcel = new \PHPExcel();

        //设置文件描述信息
        $objPHPExcel->getProperties()->setCreator('桂林灵川WEY'); //设置创建者
        $objPHPExcel->getProperties()->setLastModifiedBy(''); //设置修改者
        $objPHPExcel->getProperties()->setTitle('桂林灵川WEY保有客户列表'); //设置标题
        $objPHPExcel->getProperties()->setSubject('桂林灵川WEY保有客户列表'); //设置主题
        $objPHPExcel->getProperties()->setDescription(''); //设置描述
        $objPHPExcel->getProperties()->setKeywords('桂林灵川WEY保有客户列表');//设置关键词
        $objPHPExcel->getProperties()->setCategory('');//设置类型


        //设置数据

        
        // 设置当前sheet为第1页
        $objPHPExcel->setActiveSheetIndex(0);
        // 设置当前sheet的名称
        $objPHPExcel->getActiveSheet()->setTitle('保有客户表');

        // 列标
        $list = ['A', 'B', 'C','D','E','F','G','H','I','J','K','L','M','N','O','P',"Q","R","S","T","U","V","W","X","Y","Z"];

        // 设置第一行加粗和左对齐
        foreach ($list as $col) {
            
            //设置水平居中
            if($col != 'I'){
                $objPHPExcel->getActiveSheet()
                    ->getStyle($col)->getAlignment()
                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }

            //设置垂真居中
            $objPHPExcel->getActiveSheet()
                ->getStyle($col)->getAlignment()
                ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            
            
            // 设置第一行加粗
            $objPHPExcel->getActiveSheet()->getStyle($col.'1')
                ->getFont()->setBold(true);

            //设置第一行背景颜色
            $objPHPExcel->getActiveSheet()->getStyle($col.'1')
                ->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()->setRGB('2f6921');//设置第一行背景色为ccffff
        }

        //逐行设置行高
        for ($i=1; $i < count($data)+2; $i++) { 
            //设置行高
            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
        }

        // 设置列宽
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);//档案编号
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);//提车日期
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);//销售顾问
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);//车主名称
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(13);//车主电话
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(23);//身份证号
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(6);//性别
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(13);//生日
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(45);//地址
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(16);//省
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(8);//市
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(8);//区县
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);//职业
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);//使用人
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(13);//使用人电话
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);//车系
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(13);//配置
        $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(13);//颜色
        $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(10);//内饰
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(10);//购车价格
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(17);//车型代码
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(23);//车架号
        $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(13);//发动机号
        $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(11);//车牌号
        $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(10);//保险公司
        $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(13);//保险到期日期

        // 填充第一行数据
        $objPHPExcel->getActiveSheet()
            ->setCellValue($list[0] . '1', '档案编号')
            ->setCellValue($list[1] . '1', '提车日期')
            ->setCellValue($list[2] . '1', '销售顾问')
            ->setCellValue($list[3] . '1', '车主名称')
            ->setCellValue($list[4] . '1', '车主电话')
            ->setCellValue($list[5] . '1', '身份证号')
            ->setCellValue($list[6] . '1', '性别')
            ->setCellValue($list[7] . '1', '生日')
            ->setCellValue($list[8] . '1', '地址')
            ->setCellValue($list[9] . '1', '省')
            ->setCellValue($list[10] . '1', '市')
            ->setCellValue($list[11] . '1', '区县')
            ->setCellValue($list[12] . '1', '职业')
            ->setCellValue($list[13] . '1', '使用人')
            ->setCellValue($list[14] . '1', '使用人电话')
            ->setCellValue($list[15] . '1', '车系')
            ->setCellValue($list[16] . '1', '配置')
            ->setCellValue($list[17] . '1', '颜色')
            ->setCellValue($list[18] . '1', '内饰')
            ->setCellValue($list[19] . '1', '购车价格')
            ->setCellValue($list[20] . '1', '车型代码')
            ->setCellValue($list[21] . '1', '车架号')
            ->setCellValue($list[22] . '1', '发动机号')
            ->setCellValue($list[23] . '1', '车牌号')
            ->setCellValue($list[24] . '1', '保险公司')
            ->setCellValue($list[25] . '1', '保险到期日期');

        // 设置数据
        // 定义一个i变量，目的是在循环输出数据是控制行数
        for ($i=0; $i < count($data); $i++) { 

            //性别转换
            if($data[$i]['owner_sex'] == 1){
                $sex = "男";
            }else{
                $sex = "女";
            }

            //如果车主生日为空
            if(!empty($data[$i]['owner_birth'])){
                $owner_birth = date('Y/m/d',strtotime($data[$i]['owner_birth']));
            }else{
                $owner_birth = "";
            }

            //如果保险到期为空
            if(!empty($data[$i]['insurance_expirydate'])){
                $insurance_expirydate = date('Y/m/d',strtotime($data[$i]['insurance_expirydate']));
            }else{
                $insurance_expirydate = "";
            }
            

            //当前行数
            $row = $i + 2;

            $objPHPExcel->getActiveSheet()
                ->setCellValueExplicit($list[0] . $row, $data[$i]['file_no'],\PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit($list[1] . $row, date('Y/m/d',strtotime($data[$i]['deliver_date'])))
                ->setCellValueExplicit($list[2] . $row, $data[$i]['sales'])
                ->setCellValueExplicit($list[3] . $row, $data[$i]['owner_name'])
                ->setCellValueExplicit($list[4] . $row, $data[$i]['owner_phone'],\PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit($list[5] . $row, $data[$i]['owner_id'],\PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit($list[6] . $row, $sex)
                ->setCellValueExplicit($list[7] . $row, $owner_birth)
                ->setCellValueExplicit($list[8] . $row, $data[$i]['owner_address'])
                ->setCellValueExplicit($list[9] . $row, $data[$i]['owner_province'])
                ->setCellValueExplicit($list[10] . $row, $data[$i]['owner_city'])
                ->setCellValueExplicit($list[11] . $row, $data[$i]['owner_county'])
                ->setCellValueExplicit($list[12] . $row, $data[$i]['owner_occupation'])
                ->setCellValueExplicit($list[13] . $row, $data[$i]['user_name'])
                ->setCellValueExplicit($list[14] . $row, $data[$i]['user_phone'],\PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit($list[15] . $row, $data[$i]['vehicle'])
                ->setCellValueExplicit($list[16] . $row, $data[$i]['config'])
                ->setCellValueExplicit($list[17] . $row, $data[$i]['color'])
                ->setCellValueExplicit($list[18] . $row, $data[$i]['trim'])
                ->setCellValueExplicit($list[19] . $row, $data[$i]['final_price'],\PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit($list[20] . $row, $data[$i]['model'])
                ->setCellValueExplicit($list[21] . $row, $data[$i]['vin'])
                ->setCellValueExplicit($list[22] . $row, $data[$i]['engine'],\PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit($list[23] . $row, $data[$i]['plate_number'])
                ->setCellValueExplicit($list[24] . $row, $data[$i]['insurance_company'])
                ->setCellValueExplicit($list[25] . $row, $insurance_expirydate);
        }

        // 设置当前sheet为第1页
        $objPHPExcel->setActiveSheetIndex(0);

        //数据导出
        ob_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=桂林灵川WEY保有客户列表.xlsx');
        header('Cache-Control: max-age=1');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $file = $objWriter->save('php://output');
        exit; //必须 终止
    }

    /**
     * 客户来源分析
     *
     * @return \think\Response
     */
    public function analysis()
    {
        return view('analysis');
    }

    /**
     * 查询并导出分析
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function analysisJson(Request $request)
    {
        //根据模式调用API输入始末时间及日期标识
        $daterange = $request->param('daterange');

        //获取始末日期
        $date = DateInterval($daterange);

        //客户来源
        ///////////////////////
        $CustomerCounty = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('perfected',1)->Distinct(true)->column('owner_location','owner_county');

        //反查客户地址
        if(!empty($CustomerCounty)){
            $data['mapData'] = array();
            //反查客户地址
            foreach ($CustomerCounty as $key => $value) {
                if(!empty($value)){
                    $CustomerCountyData['lnglat'] = $value;
                    $district = $key;//区县
                    $CustomerCountyData['count'] = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('owner_county',$district)->count();
                    $location = explode(',',  $CustomerCountyData['lnglat']);
                    $CustomerCountyData['title'] = $district;
                    array_push($data['mapData'], $CustomerCountyData);
                }
            }
        }else{
            $data['mapData'] = array();
        }
        //////////////////////

        //客户性别owner_sex
        /////////////////////
        $CustomerSexCountMale = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('owner_sex',1)->where('perfected',1)->count();
        $CustomerSexCountFemale = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('owner_sex',0)->count();
        $CustomerSex['label'] = array('男','女');
        $CustomerSex['data'] = array($CustomerSexCountMale,$CustomerSexCountFemale);
        $data['sex'] = $CustomerSex;
        /////////////////////

        //客户年龄层owner_birth
        /////////////////////////////
        $CustomerBirth = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('perfected',1)->column('owner_birth');

        $CustomerAgeLayer20 = 0;
        $CustomerAgeLayer25 = 0;
        $CustomerAgeLayer30 = 0;
        $CustomerAgeLayer35 = 0;
        $CustomerAgeLayer40 = 0;
        $CustomerAgeLayer50 = 0;
        $CustomerAgeLayer80 = 0;
        if(!empty($CustomerBirth)){
            for ($i=0; $i < count($CustomerBirth); $i++) { 
                if(!empty($CustomerBirth[$i])){
                    $age = date('Y') - date('Y',strtotime($CustomerBirth[$i]));
                    if($age < 20){
                        $CustomerAgeLayer20++;
                    }
                    if($age >= 20 && $age <25){
                        $CustomerAgeLayer25++;
                    }
                    if($age >= 25 && $age <30){
                        $CustomerAgeLayer30++;
                    }
                    if($age >= 30 && $age <35){
                        $CustomerAgeLayer35++;
                    }
                    if($age >= 35 && $age <40){
                        $CustomerAgeLayer40++;
                    }
                    if($age >= 40 && $age <50){
                        $CustomerAgeLayer50++;
                    }
                    if($age >= 50 && $age <80){
                        $CustomerAgeLayer80++;
                    }
                }
            }
        }      
        $CustomerAge['label'] = array('20岁以下','20-25岁','25-30岁','30-35岁','35至40岁','40至50岁','50-80岁');
        $CustomerAge['data'] = array($CustomerAgeLayer20,$CustomerAgeLayer25,$CustomerAgeLayer30,$CustomerAgeLayer35,$CustomerAgeLayer40,$CustomerAgeLayer50,$CustomerAgeLayer80);
        $data['age'] = $CustomerAge;
        ////////////////////////////////////////

        //客户职业owner_occupation
        ///////////////////////////////////////
        $CustomerOccupation = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('owner_occupation','NOTNULL')->where('perfected',1)->Distinct(true)->column('owner_occupation');
        $Occupation['label'] = array();
        $Occupation['data'] = array();
        if(!empty($CustomerOccupation)){
            for ($i=0; $i < count($CustomerOccupation); $i++) { 
                $OccupationCount = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('owner_occupation',$CustomerOccupation[$i])->count();
                array_push($Occupation['label'], $CustomerOccupation[$i]);
                array_push($Occupation['data'], $OccupationCount);
            }
        }
        $data['occupation'] = $Occupation;
        //////////////////////

        //车型比例
        //////////////////////
        $vehicleTitle = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('perfected',1)->Distinct(true)->column('vehicle');
        $vehicle['label'] = array();
        $vehicle['data'] = array();
        if(!empty($vehicleTitle)){
            foreach($vehicleTitle as $key => $value) {
                $vehicleCount = CustomerModel::whereTime('deliver_date','between',[$date['thisStart'],$date['thisEnd']])->where('vehicle',$value)->count();
                 array_push($vehicle['label'], $value);
                array_push($vehicle['data'], $vehicleCount);
            }
        }
        $data['vehicle'] = $vehicle;
        ////////////////////////

        return json($data);
    }

}
